﻿using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using feather.attr;

namespace feather.unit
{
    /// <summary>
    /// SQL相关的Action方法
    /// </summary>
    public class SQLActionUnit
    {
        /// <summary>
        /// 执行SQL查询，返回第一行第一列的数据
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="args">参数集合</param>
        /// <returns></returns>
        [ActionMethod]
        public object sqlQueryOne(string sql, [InputParam("params")] IDictionary<string, object> args)
        {
            object result = null;
            using (DbCommand dbCommand = buildCommand(sql, args))
            {
                result = dbCommand.ExecuteScalar();
            }
            return result;
        }

        /// <summary>
        /// 执行SQL查询，返回第一行的数据
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="args">参数集合</param>
        /// <returns></returns>
        [ActionMethod]
        public object sqlQueryObject(string sql, [InputParam("params")] IDictionary<string, object> args)
        {
            IDictionary<string, object> result = null;

            using (DbCommand dbCommand = buildCommand(sql, args))
            {
                using (DbDataReader dbDataReader = dbCommand.ExecuteReader())
                {
                    if (dbDataReader.HasRows && dbDataReader.Read())
                    {
                        result = new Dictionary<string, object>();

                        for (int i = 0; i < dbDataReader.FieldCount; i++)
                        {
                            if (!dbDataReader.IsDBNull(i))
                                result.Add(dbDataReader.GetName(i), dbDataReader.GetValue(i));
                        }
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 执行SQL查询，以DataTable的形式返回所有数据
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="args">参数集合</param>
        /// <returns></returns>
        [ActionMethod]
        public object sqlQueryTable(string sql, [InputParam("params")] IDictionary<string, object> args)
        {
            DataTable result = new DataTable();

            using (DbCommand dbCommand = buildCommand(sql, args))
            {
                using (DbDataReader dbDataReader = dbCommand.ExecuteReader())
                {
                    for (int i = 0; i < dbDataReader.FieldCount; i++)
                        result.Columns.Add(dbDataReader.GetName(i));

                    while (dbDataReader.HasRows && dbDataReader.Read())
                    {
                        DataRow dataRow = result.NewRow();

                        for (int i = 0; i < dbDataReader.FieldCount; i++)
                        {
                            dataRow[i] = dbDataReader.GetValue(i);
                        }

                        result.Rows.Add(dataRow);
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 执行SQL查询，以IList的形式返回所有数据
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="args">参数集合</param>
        /// <returns></returns>
        [ActionMethod]
        public object sqlQueryList(string sql, [InputParam("params")] IDictionary<string, object> args)
        {
            IList<IDictionary<string, object>> result = new List<IDictionary<string, object>>();
            using (DbCommand dbCommand = buildCommand(sql, args))
            {
                using (DbDataReader dbDataReader = dbCommand.ExecuteReader())
                {
                    string[] filedNames = new string[dbDataReader.FieldCount];
                    for (int i = 0; i < filedNames.Length; i++)
                    {
                        filedNames[i] = dbDataReader.GetName(i);
                    }

                    while (dbDataReader.HasRows && dbDataReader.Read())
                    {
                        IDictionary<string, object> record = new Dictionary<string, object>();

                        for (int i = 0; i < filedNames.Length; i++)
                        {
                            if (!dbDataReader.IsDBNull(i))
                                record.Add(filedNames[i], dbDataReader.GetValue(i));
                            else
                                record.Add(filedNames[i], null);
                        }

                        result.Add(record);
                    }

                    if (result.Count == 0)
                        result = null;
                }
            }
            return result;
        }

        /// <summary>
        /// 使用DataTable数据创建数据表
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="data">数据</param>
        [ActionMethod]
        public void createTable(string tableName, DataTable data)
        {
            string fileds = string.Empty;
            string insertFileds = string.Empty;
            for(int i = 0; i < data.Columns.Count; i++)
            {
                string e = data.Columns[i].ColumnName;
                if (fileds.Length > 0)
                {
                    fileds += ",";
                    insertFileds += ",";
                }

                fileds += string.Format("`{0}` text", e);
                insertFileds += string.Format("`{0}`", e);
            }

            using (DbCommand dbCommand = buildCommand(string.Format("create table {0} ({1})", tableName, fileds), null))
            {
                dbCommand.ExecuteNonQuery();
            }

            string ds = string.Empty;
            for (int i = 0; i < data.Rows.Count; i++)
            {
                DataRow dataRow = data.Rows[i];
                if (ds.Length > 0)
                    ds += ",";

                string d = string.Empty;
                for (int j = 0; j < dataRow.ItemArray.Length; j++)
                {
                    string e = dataRow[j] as string;
                    if (d.Length > 0)
                        d += ",";
                    d += string.Format("'{0}'", e);
                }

                ds += string.Format("({0})", d);
            }

            using (DbCommand dbCommand = buildCommand(string.Format("begin; insert into {0}({1}) values {2}; commit;", tableName, insertFileds, ds), null))
            {
                dbCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行SQL更新，包括Insert和Delete语句
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="args">参数集合</param>
        /// <returns></returns>
        [ActionMethod]
        public int sqlUpdate(string sql, [InputParam("params")]IDictionary<string, object> args)
        {
            using (DbCommand dbCommand = buildCommand(sql, args))
            {
                return dbCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 组装SQL和参数，返回DbCommand对象
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="args">参数集合</param>
        /// <returns>DbCommand对象</returns>
        private DbCommand buildCommand(string sql, IDictionary<string, object> args)
        {
            DbCommand dbCommand = DbConnection.CreateCommand();
            dbCommand.CommandText = sql;

            if (args != null)
            {
                foreach (KeyValuePair<string, object> e in args)
                {
                    DbParameter dbParam = dbCommand.CreateParameter();
                    dbParam.ParameterName = ":" + e.Key;
                    dbParam.Value = e.Value;
                    dbCommand.Parameters.Add(dbParam);
                }
            }

            return dbCommand;
        }
        
        /// <summary>
        /// 数据连接对象
        /// </summary>
        public DbConnection DbConnection { get; set; }
    }
}
